package com.wchen.gdoc.generate.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.*;

/**
 * c3p0工具类
 *
 * @author  wchen
 * @date    2019/11/7
 */
public class C3p0Util {

    private static DataSource dataSource = new ComboPooledDataSource();

    //在静态代码块中创建数据库连接池
    static{
        try{
            //使用C3P0的命名配置来创建数据源
            dataSource = new ComboPooledDataSource("MySQL");

        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }


    /**
     * @Method: release
     * @Description: 释放资源，
     * 释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
     *
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs != null){
            try{
                //关闭存储查询结果的ResultSet对象
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(st != null){
            try{
                //关闭负责执行SQL命令的Statement对象
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try{
                //将Connection连接对象还给数据库连接池
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 获取连接池连接
     *
     * @return
     */
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取连接失败");
        }
    }


    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            //获取数据库连接
            conn = C3p0Util.getConnection();
            String sql = "select * from sys_config where config_key = ?";
            st = conn.prepareStatement(sql);
            st.setString(1, "ACCOUNT_EXTRACT_NUM_LIMIT");
            rs = st.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String gender = rs.getString(3);
                System.out.println("A:"+id+" B："+name+" C："+gender);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            C3p0Util.release(conn, st, rs);
        }
    }

}
